package com.pig4cloud.pig.ads.dao;

import cn.hutool.core.lang.Pair;
import com.pig4cloud.pig.api.entity.AdMaterialReportDO;
import com.pig4cloud.pig.api.vo.AdMaterialReportVo;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Title null.java
 * @Package com.pig4cloud.pig.ads.dao
 * @Author 马嘉祺
 * @Date 2021/8/27 10:21
 * @Description
 */
@Slf4j
@Component
public class AdMaterialReportDao {

	private static final boolean ANY_ALL = Boolean.TRUE;

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;

	public List<AdMaterialReportDO> queryMaterialReportList(AdMaterialReportVo amr) {
		Collection<String> groupBys = null != amr.getGroupBys() ? amr.getGroupBys() : Collections.emptyList();
		Map<String, Long> creativeCountMap;
		if (groupBys.contains("material") || groupBys.contains("parentchl") || groupBys.contains("osType")) {
			String creativeCountSql = getCreativeCountSql(amr).toString();
			log.info("creativeCountSql:\n[{}]", creativeCountSql);
			creativeCountMap = clickhouseTemplate.query(creativeCountSql, creativeObjectRelationMapping(amr)).stream().collect(Collectors.toMap(Pair::getKey, Pair::getValue, (k1, k2) -> k1));
		} else {
			// 没有分组代表为汇总查询，此时不需要查询创意总数进行使用率计算
			creativeCountMap = Collections.emptyMap();
		}
		String materialListSql = getMaterialListSql(amr).toString();
		log.info("materialListSql:\n[{}]", materialListSql);
		return clickhouseTemplate.query(materialListSql, materialObjectRelationMapping(amr, creativeCountMap));
	}

	private StringBuilder getCreativeCountSql(AdMaterialReportVo amr) {
		Collection<String> groupBys = Optional.ofNullable(amr.getGroupBys()).orElse(Collections.emptyList());

		String selectByColumn = (groupBys.contains("parentchl") ? ", IFNULL(ad.parentchl, '') AS chncode" : "") + (groupBys.contains("osType") ? ", IFNULL(CAST(ad.os AS Nullable(String)), '') AS os_type" : "");
		String groupsByColumn = (groupBys.contains("parentchl") ? ", ad.parentchl" : "") + (groupBys.contains("osType") ? ", ad.os" : "");
		String wheresByColumn = (StringUtils.isNotEmpty(amr.getParentchl()) ? " AND ad.parentchl='" + amr.getParentchl() + "'" : "") + (Objects.nonNull(amr.getOsType()) ? " AND ad.os=" + amr.getOsType() : "");

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n");
		sql.append("    cre.group_key AS group_key").append(selectByColumn).append(", CAST(COUNT(cre.creative_id) AS Nullable(UInt64)) AS creative_count\n");
		sql.append("FROM\n");
		sql.append("    (\n");
		sql.append("        SELECT\n");
		sql.append("            'prefix' AS group_key, CAST(1 AS UInt16) AS ctype, ad_id AS adid, creative_id\n");
		sql.append("        FROM\n");
		sql.append("            odsmysql_ad_creative\n");
		sql.append("        UNION ALL\n");
		sql.append("        SELECT\n");
		sql.append("            'prefix' AS group_key, CAST(1 AS UInt16) AS ctype, adgroup_id AS adid, ad_id AS creative_id\n");
		sql.append("        FROM\n");
		sql.append("            odsmysql_gdt_ad\n");
		sql.append("    ) cre\n");
		sql.append("    LEFT JOIN (\n");
		sql.append("        SELECT\n");
		sql.append("            ctype AS ctype, CAST(parentchl AS Nullable(String)) AS parentchl, CAST(gameid AS Nullable(UInt16)) AS gameid, adid AS adid, CAST(g.os AS Nullable(UInt16)) AS os\n");
		sql.append("        FROM\n");
		sql.append("            ad_ptype ad\n");
		sql.append("            LEFT JOIN v_odsmysql_wan_game g ON ad.gameid=g.id\n");
		sql.append("    ) ad ON cre.ctype=ad.ctype AND CAST(cre.adid AS String)=ad.adid\n");
		sql.append("WHERE\n");
		sql.append("    1 = 1").append(wheresByColumn).append('\n');
		sql.append("GROUP BY\n");
		sql.append("    cre.group_key").append(groupsByColumn);
		return sql;
	}

	private StringBuilder getMaterialListSql(AdMaterialReportVo amr) {
		int sdate = Integer.parseInt(amr.getSdate().replace("-", ""));
		int edate = Integer.parseInt(amr.getEdate().replace("-", ""));
		Collection<String> groupBys = null != amr.getGroupBys() ? amr.getGroupBys() : Collections.emptyList();

		final String whe_mat_snippet = materialWhereSnippet(amr);

		final boolean typ_mat = groupBys.contains("material"), typ_chn = groupBys.contains("parentchl"), typ_sos = groupBys.contains("osType"), typ_exi = typ_mat || typ_chn || typ_sos;
		final String join_key_snippet1 = typ_mat ? ", toString(amc.material_id)" : StringUtils.EMPTY;
		final String join_key_snippet2 = typ_chn ? ", IFNULL(amc.chncode, '')" : StringUtils.EMPTY;
		final String join_key_snippet3 = typ_sos ? ", IFNULL(toString(amc.os_type), '')" : StringUtils.EMPTY;

		final boolean whe_chn = StringUtils.isNotEmpty(amr.getParentchl()), whe_sos = Objects.nonNull(amr.getOsType());
		final String whe_chn_snippet = whe_chn ? " AND amc.chncode='" + amr.getParentchl() + '\'' : StringUtils.EMPTY;
		final String whe_sos_snippet = whe_sos ? " AND amc.os_type=" + amr.getOsType() : StringUtils.EMPTY;

		StringBuilder sqlSb = new StringBuilder();
		SqlSnippet sqlSnippet = new SqlSnippet(sqlSb);

		sqlSnippet.add(ANY_ALL, "\nSELECT\n");
		sqlSnippet.add(typ_exi, "    -- 素材相关指标\n");
		sqlSnippet.add(typ_mat, "    material_id, material_type, material_name, file_url, image_url, creator_id, creator_name, maker_id, maker_name, width, height, duration, pgid, pgname, make_type,\n"); // 按素材分组查询条件
		sqlSnippet.add(typ_chn, "    chncode, chnname,\n");
		sqlSnippet.add(typ_sos, "    os_type,\n");
		sqlSnippet.add(typ_mat, "    used_day_count, used_creative_count,\n");
		sqlSnippet.add(ANY_ALL, "    -- 广告消耗相关指标\n");
		sqlSnippet.add(ANY_ALL, "    cost, rudecost, show_count, click_count, play10per_count, play25per_count, play50per_count, play75per_count, play95per_count, play99per_count, play_total_count, play_valid_count, play_over_count,\n");
		sqlSnippet.add(ANY_ALL, "    -- 设备相关指标\n");
		sqlSnippet.add(ANY_ALL, "    active_count, register_count, stay2_user_count, stay7_user_count, stay30_user_count, new_pay_users, new_pay_fee, new_pay_givemoney, total_fee, total_givemoney\n");
		sqlSnippet.add(ANY_ALL, "FROM\n");
		sqlSnippet.add(ANY_ALL, "    -- 广告消耗及视频播放相关指标\n");
		sqlSnippet.add(ANY_ALL, "    (\n");
		sqlSnippet.add(ANY_ALL, "        SELECT\n");
		sqlSnippet.add(ANY_ALL, "            CONCAT('', ''").add(join_key_snippet1).add(join_key_snippet2).add(join_key_snippet3).add(") AS join_key,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(SUM(cdr.cost) AS Nullable(Decimal(18, 2))) AS cost,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(SUM(cdr.rudecost) AS Nullable(Decimal(18, 2))) AS rudecost,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.show_count) AS show_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.click_count) AS click_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play10per_count) AS play10per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play25per_count) AS play25per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play50per_count) AS play50per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play75per_count) AS play75per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play95per_count) AS play95per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play99per_count) AS play99per_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play_total_count) AS play_total_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play_valid_count) AS play_valid_count,\n");
		sqlSnippet.add(ANY_ALL, "            SUM(cdr.play100per_count) AS play_over_count\n");
		sqlSnippet.add(ANY_ALL, "        FROM\n");
		sqlSnippet.add(ANY_ALL, "            odsmysql_ad_material msam\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN v_ad_material_creative amc ON msam.id=amc.material_id\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN v_creative_day_report_rebate cdr ON amc.ctype=cdr.ctype AND amc.creative_id=cdr.creative_id\n");
		sqlSnippet.add(ANY_ALL, "        WHERE\n");
		sqlSnippet.add(ANY_ALL, "            msam.screen_type != 7 AND cdr.cost > 0 AND cdr.date >= ").add(sdate).add(" AND cdr.date <= ").add(edate).add(whe_chn_snippet).add(whe_sos_snippet).add(whe_mat_snippet).add('\n');
		sqlSnippet.add(ANY_ALL, "        GROUP BY\n");
		sqlSnippet.add(typ_mat, "            amc.material_id,\n");
		sqlSnippet.add(typ_chn, "            amc.chncode,\n");
		sqlSnippet.add(typ_sos, "            amc.os_type,\n");
		sqlSnippet.add(ANY_ALL, "            1\n");
		sqlSnippet.add(ANY_ALL, "    ) cdr\n");
		sqlSnippet.add(ANY_ALL, "    -- 设备注册相关指标\n");
		sqlSnippet.add(ANY_ALL, "    LEFT JOIN (\n");
		sqlSnippet.add(ANY_ALL, "        SELECT\n");
		sqlSnippet.add(ANY_ALL, "            CONCAT('', ''").add(join_key_snippet1).add(join_key_snippet2).add(join_key_snippet3).add(") AS join_key,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(COUNT(DISTINCT dreg.kid) AS Nullable(UInt64)) AS active_count, CAST(COUNT(DISTINCT ureg.kid) AS Nullable(UInt64)) AS register_count,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(COUNT(DISTINCT stay2_kid) AS Nullable(UInt64)) AS stay2_user_count, CAST(COUNT(DISTINCT stay7_kid) AS Nullable(UInt64)) AS stay7_user_count, CAST(COUNT(DISTINCT stay30_kid) AS Nullable(UInt64)) AS stay30_user_count,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(COUNT(DISTINCT new_pay_kid) AS Nullable(UInt64)) AS new_pay_users, CAST(SUM(new_pay_fee) AS Nullable(Decimal(18, 2))) AS new_pay_fee, CAST(SUM(new_pay_givemoney) AS Nullable(Decimal(18, 2))) AS new_pay_givemoney,\n");
		sqlSnippet.add(ANY_ALL, "            CAST(SUM(total_fee) AS Nullable(Decimal(18, 2))) AS total_fee, CAST(SUM(total_givemoney) AS Nullable(Decimal(18, 2))) AS total_givemoney\n");
		sqlSnippet.add(ANY_ALL, "        FROM\n");
		sqlSnippet.add(ANY_ALL, "            odsmysql_ad_material msam\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN v_ad_material_creative amc ON msam.id=amc.material_id\n");
		sqlSnippet.add(ANY_ALL, "            -- 设备注册相关表关联\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN v_thirty_ad_device tad ON amc.ctype=CAST(tad.ctype AS UInt64) AND amc.creative_id=tad.cid\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN thirty_game_device_reg dreg ON tad.day=dreg.day AND tad.uuid=dreg.uuid AND tad.pgid=dreg.pgid\n");
		sqlSnippet.add(ANY_ALL, "            -- 账号注册归因结果关联\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN (\n");
		sqlSnippet.add(ANY_ALL, "                SELECT\n");
		sqlSnippet.add(ANY_ALL, "                    DISTINCT argMax(dreg.kid, dreg.receivetime) AS kid\n");
		sqlSnippet.add(ANY_ALL, "                FROM\n");
		sqlSnippet.add(ANY_ALL, "                    thirty_game_device_reg dreg\n");
		sqlSnippet.add(ANY_ALL, "                    INNER JOIN odsmysql_wan_game g ON dreg.pgid=g.pgid\n");
		sqlSnippet.add(ANY_ALL, "                    INNER JOIN v_game_account_reg ureg ON dreg.uuid=ureg.uuid AND g.id=ureg.gameid\n");
		sqlSnippet.add(ANY_ALL, "                WHERE\n");
		sqlSnippet.add(ANY_ALL, "                    ureg.receivetimes >= dreg.receivetime AND dreg.day >= ").add(sdate).add(" AND dreg.day <= ").add(edate).add('\n');
		sqlSnippet.add(ANY_ALL, "                GROUP BY\n");
		sqlSnippet.add(ANY_ALL, "                    ureg.kid\n");
		sqlSnippet.add(ANY_ALL, "            ) ureg ON dreg.kid=ureg.kid\n");
		sqlSnippet.add(ANY_ALL, "            -- 用户留存归因结果关联\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN (\n");
		sqlSnippet.add(ANY_ALL, "                SELECT\n");
		sqlSnippet.add(ANY_ALL, "                    kid, any(stay2_kid) AS stay2_kid, any(stay7_kid) AS stay7_kid, any(stay30_kid) AS stay30_kid\n");
		sqlSnippet.add(ANY_ALL, "                FROM\n");
		sqlSnippet.add(ANY_ALL, "                    (\n");
		sqlSnippet.add(ANY_ALL, "                        SELECT\n");
		sqlSnippet.add(ANY_ALL, "                            argMax(dreg.kid, dreg.receivetime) AS kid, parseDateTimeBestEffort(toString(argMax(dreg.day, dreg.receivetime))) AS dreg_day, argMax(ul.day, dreg.receivetime) AS ul_day,\n");
		sqlSnippet.add(ANY_ALL, "                            IF(toYYYYMMDD(addDays(dreg_day, 1)) = ul_day, kid, NULL) AS stay2_kid, IF(toYYYYMMDD(addDays(dreg_day, 6)) = ul_day, kid, NULL) AS stay7_kid,\n");
		sqlSnippet.add(ANY_ALL, "                            IF(toYYYYMMDD(addDays(dreg_day, 29)) = ul_day, kid, NULL) AS stay30_kid\n");
		sqlSnippet.add(ANY_ALL, "                        FROM\n");
		sqlSnippet.add(ANY_ALL, "                            user_login ul\n");
		sqlSnippet.add(ANY_ALL, "                            INNER JOIN odsmysql_wan_game g ON ul.gameid=g.id\n");
		sqlSnippet.add(ANY_ALL, "                            INNER JOIN thirty_game_device_reg dreg ON ul.uuid=dreg.uuid AND g.pgid=dreg.pgid\n");
		sqlSnippet.add(ANY_ALL, "                        WHERE\n");
		sqlSnippet.add(ANY_ALL, "                            ul.receivetime >= dreg.receivetime AND dreg.day >= ").add(sdate).add(" AND dreg.day <= ").add(edate).add('\n');
		sqlSnippet.add(ANY_ALL, "                        GROUP BY\n");
		sqlSnippet.add(ANY_ALL, "                            ul.kid\n");
		sqlSnippet.add(ANY_ALL, "                    )\n");
		sqlSnippet.add(ANY_ALL, "                GROUP BY\n");
		sqlSnippet.add(ANY_ALL, "                    kid\n");
		sqlSnippet.add(ANY_ALL, "            ) stay ON dreg.kid=stay.kid\n");
		sqlSnippet.add(ANY_ALL, "            -- 用户充值归因结果关联\n");
		sqlSnippet.add(ANY_ALL, "            LEFT JOIN (\n");
		sqlSnippet.add(ANY_ALL, "                SELECT\n");
		sqlSnippet.add(ANY_ALL, "                    argMax(dreg.kid, dreg.receivetime) AS kid, argMax(dreg.day, dreg.receivetime) AS dreg_day, argMax(pay.day, dreg.receivetime) AS pay_day,\n");
		sqlSnippet.add(ANY_ALL, "                    argMax(pay.fee, dreg.receivetime) AS total_fee, argMax(pay.givemoney, dreg.receivetime) AS total_givemoney, IF(dreg_day = pay_day, kid, NULL) AS new_pay_kid,\n");
		sqlSnippet.add(ANY_ALL, "                    IF(dreg_day = pay_day, total_fee, NULL) AS new_pay_fee, IF(dreg_day = pay_day, total_givemoney, NULL) AS new_pay_givemoney\n");
		sqlSnippet.add(ANY_ALL, "                FROM\n");
		sqlSnippet.add(ANY_ALL, "                    original_user_recharge pay\n");
		sqlSnippet.add(ANY_ALL, "                    INNER JOIN odsmysql_wan_game g ON pay.gameid=g.id\n");
		sqlSnippet.add(ANY_ALL, "                    INNER JOIN thirty_game_device_reg dreg ON pay.uuid=dreg.uuid AND g.pgid=dreg.pgid\n");
		sqlSnippet.add(ANY_ALL, "                WHERE\n");
		sqlSnippet.add(ANY_ALL, "                    pay.paytime >= dreg.receivetime AND dreg.day >= ").add(sdate).add(" AND dreg.day <= ").add(edate).add('\n');
		sqlSnippet.add(ANY_ALL, "                GROUP BY\n");
		sqlSnippet.add(ANY_ALL, "                    pay.kid\n");
		sqlSnippet.add(ANY_ALL, "            ) pay ON dreg.kid=pay.kid\n");
		sqlSnippet.add(ANY_ALL, "        WHERE\n");
		sqlSnippet.add(ANY_ALL, "             dreg.day >= ").add(sdate).add(" AND dreg.day <= ").add(edate).add(" AND msam.screen_type != 7").add(whe_chn_snippet).add(whe_sos_snippet).add(whe_mat_snippet).add('\n');
		sqlSnippet.add(ANY_ALL, "        GROUP BY\n");
		sqlSnippet.add(typ_mat, "            amc.material_id,\n");
		sqlSnippet.add(typ_chn, "            amc.chncode,\n");
		sqlSnippet.add(typ_sos, "            amc.os_type,\n");
		sqlSnippet.add(ANY_ALL, "            1\n");
		sqlSnippet.add(ANY_ALL, "    ) reg ON cdr.join_key=reg.join_key\n");
		sqlSnippet.add(typ_exi, "    LEFT JOIN (\n");
		sqlSnippet.add(typ_exi, "        SELECT\n");
		sqlSnippet.add(typ_exi, "            CONCAT('', ''").add(typ_exi, join_key_snippet1).add(typ_exi, join_key_snippet2).add(typ_exi, join_key_snippet3).add(typ_exi, ") AS join_key,\n");
		sqlSnippet.add(typ_mat, "            amc.material_id AS material_id, msam.type AS material_type, msam.name AS material_name, msam.file_url AS file_url, msam.image_url AS image_url, CAST(cuser.user_id AS Nullable(UInt64)) AS creator_id,\n");
		sqlSnippet.add(typ_mat, "            CAST(cuser.real_name AS Nullable(String)) AS creator_name, CAST(muser.user_id AS Nullable(UInt64)) AS maker_id, CAST(muser.real_name AS Nullable(String)) AS maker_name, msam.width AS width,\n");
		sqlSnippet.add(typ_mat, "            msam.height AS height, msam.duration AS duration, mspg.id AS pgid, mspg.gname AS pgname, msam.make_type AS make_type,\n");
		sqlSnippet.add(typ_chn, "            amc.chncode AS chncode, amc.chnname AS chnname,\n");
		sqlSnippet.add(typ_sos, "            amc.os_type AS os_type,\n");
		sqlSnippet.add(typ_exi, "            COUNT(DISTINCT cdr.date) AS used_day_count, COUNT(DISTINCT cdr.creative_id) AS used_creative_count\n");
		sqlSnippet.add(typ_exi, "        FROM\n");
		sqlSnippet.add(typ_exi, "            odsmysql_ad_material msam\n");
		sqlSnippet.add(typ_exi, "            LEFT JOIN v_ad_material_creative amc ON msam.id=amc.material_id\n");
		sqlSnippet.add(typ_exi, "            LEFT JOIN v_creative_day_report cdr ON amc.ctype=cdr.ctype AND amc.creative_id=cdr.creative_id\n");
		sqlSnippet.add(typ_mat, "            LEFT JOIN odsmysql_parent_game mspg ON msam.main_game_id=mspg.id\n");
		sqlSnippet.add(typ_mat, "            LEFT JOIN odsmysql_sys_user cuser ON msam.creator_id=CAST(cuser.user_id AS UInt32)\n");
		sqlSnippet.add(typ_mat, "            LEFT JOIN odsmysql_sys_user muser ON msam.maker_id=CAST(muser.user_id AS UInt32)\n");
		sqlSnippet.add(typ_exi, "        WHERE\n");
		sqlSnippet.add(typ_exi, "            cdr.cost > 0 AND msam.screen_type != 7").add(typ_exi, whe_chn_snippet).add(typ_exi, whe_sos_snippet).add(typ_exi, whe_mat_snippet).add(typ_exi, '\n');
		sqlSnippet.add(typ_exi, "        GROUP BY\n");
		sqlSnippet.add(typ_mat, "            amc.material_id, msam.type, msam.name, msam.file_url, msam.image_url, cuser.user_id, cuser.real_name, muser.user_id, muser.real_name, msam.width, msam.height, msam.duration, mspg.id, mspg.gname, msam.make_type,\n");
		sqlSnippet.add(typ_chn, "            amc.chncode, amc.chnname,\n");
		sqlSnippet.add(typ_sos, "            amc.os_type,\n");
		sqlSnippet.add(typ_exi, "            1\n");
		sqlSnippet.add(typ_exi, "    ) used ON cdr.join_key=used.join_key\n");
		sqlSnippet.add(typ_mat, "ORDER BY\n");
		sqlSnippet.add(typ_mat, "    material_id DESC\n");
		return sqlSb;
	}

	static class SqlSnippet {

		private final StringBuilder sb;

		public SqlSnippet(StringBuilder sb) {
			this.sb = sb;
		}

		public SqlSnippet add(String value) {
			sb.append(value);
			return this;
		}

		public SqlSnippet add(char value) {
			sb.append(value);
			return this;
		}

		public SqlSnippet add(int value) {
			sb.append(value);
			return this;
		}

		public SqlSnippet add(boolean condition, String value) {
			if (condition) {
				sb.append(value);
			}
			return this;
		}

		public SqlSnippet add(boolean condition, char value) {
			if (condition) {
				sb.append(value);
			}
			return this;
		}

		public SqlSnippet add(boolean condition, int value) {
			if (condition) {
				sb.append(value);
			}
			return this;
		}

	}

	private String materialWhereSnippet(AdMaterialReportVo amr) {
		StringBuilder whereSql = new StringBuilder();
		if (Objects.nonNull(amr.getMaterialType())) {
			whereSql.append(" AND msam.type=").append(amr.getMaterialType());
		}
		if (Objects.nonNull(amr.getCreatorId())) {
			whereSql.append(" AND msam.creator_id=").append(amr.getCreatorId());
		}
		if (Objects.nonNull(amr.getMakerId())) {
			whereSql.append(" AND msam.maker_id=").append(amr.getMakerId());
		}
		if (Objects.nonNull(amr.getPgid())) {
			whereSql.append(" AND msam.main_game_id=").append(amr.getPgid());
		}
		if (Objects.nonNull(amr.getMakeType())) {
			whereSql.append(" AND msam.make_type=").append(amr.getMakeType());
		}
		if (StringUtils.isNotEmpty(amr.getMaterialName())) {
			whereSql.append(" AND msam.name LIKE '%").append(amr.getMaterialName()).append("%'");
		}
		if (StringUtils.isNotEmpty(amr.getVideoWidth())) {
			whereSql.append(" AND msam.width='").append(amr.getVideoWidth()).append('\'');
		}
		if (StringUtils.isNotEmpty(amr.getVideoHeight())) {
			whereSql.append(" AND msam.height='").append(amr.getVideoHeight()).append('\'');
		}
		return whereSql.toString();
	}

	private RowMapper<Pair<String, Long>> creativeObjectRelationMapping(AdMaterialReportVo amr) {
		return (rs, idx) -> {
			int columnIdx = 0;
			Collection<String> groupBys = Optional.ofNullable(amr.getGroupBys()).orElse(Collections.emptyList());
			StringBuilder key = new StringBuilder();
			key.append(rs.getString(++columnIdx));
			if (groupBys.contains("parentchl")) {
				key.append(rs.getString("chncode"));
			}
			if (groupBys.contains("osType")) {
				key.append(rs.getString("os_type"));
			}
			return Pair.of(key.toString(), rs.getLong("creative_count"));
		};
	}

	private RowMapper<AdMaterialReportDO> materialObjectRelationMapping(AdMaterialReportVo amr, Map<String, Long> creativeCountMap) {
		return (rs, idx) -> {
			int columnIdx = 0;
			AdMaterialReportDO material = new AdMaterialReportDO();
			Collection<String> groupBys = Optional.ofNullable(amr.getGroupBys()).orElse(Collections.emptyList());
			if (!groupBys.isEmpty()) {
				if (groupBys.contains("material")) {
					material.setMaterialId(getLong(rs.getObject(++columnIdx)));
					material.setMaterialType(getInteger(rs.getObject(++columnIdx)));
					material.setMaterialName(rs.getString(++columnIdx));
					material.setFileUrl(rs.getString(++columnIdx));
					material.setImageUrl(rs.getString(++columnIdx));
					material.setCreatorId(getInteger(rs.getObject(++columnIdx)));
					material.setCreatorName(rs.getString(++columnIdx));
					material.setMakerId(getInteger(rs.getObject(++columnIdx)));
					material.setMakerName(rs.getString(++columnIdx));
					material.setWidth(rs.getString(++columnIdx));
					material.setHeight(rs.getString(++columnIdx));
					material.setMaterialPixel(material.getWidth() + 'x' + material.getHeight());
					material.setDuration(rs.getString(++columnIdx));
					material.setPgid(getLong(rs.getObject(++columnIdx)));
					material.setPgname(rs.getString(++columnIdx));
					material.setMakeType(getInteger(rs.getObject(++columnIdx)));
				}
				if (groupBys.contains("parentchl")) {
					material.setChncode(rs.getString(++columnIdx));
					material.setChnname(rs.getString(++columnIdx));
				}
				if (groupBys.contains("osType")) {
					material.setOsType(getInteger(rs.getObject(++columnIdx)));
				}
				if (groupBys.contains("material")) {
					material.setUsedDayCount(getLong(rs.getObject(++columnIdx)));
					material.setUsedCreativeCount(getLong(rs.getObject(++columnIdx)));
				}
			}

			material.setCost(rs.getBigDecimal(++columnIdx));
			material.setRudecost(rs.getBigDecimal(++columnIdx));
			material.setShowCount(getLong(rs.getObject(++columnIdx)));
			material.setClickCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay10perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay25perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay50perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay75perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay95perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlay99perCount(getLong(rs.getObject(++columnIdx)));
			material.setPlayTotalCount(getLong(rs.getObject(++columnIdx)));
			material.setPlayValidCount(getLong(rs.getObject(++columnIdx)));
			material.setPlayOverCount(getLong(rs.getObject(++columnIdx)));

			material.setActiveCount(getLong(rs.getObject(++columnIdx)));
			material.setRegisterCount(getLong(rs.getObject(++columnIdx)));
			material.setStay2UserCount(getLong(rs.getObject(++columnIdx)));
			material.setStay7UserCount(getLong(rs.getObject(++columnIdx)));
			material.setStay30UserCount(getLong(rs.getObject(++columnIdx)));
			material.setNewPayUsers(getLong(rs.getObject(++columnIdx)));
			material.setNewPayFee(rs.getBigDecimal(++columnIdx));
			material.setNewPayGivemoney(rs.getBigDecimal(++columnIdx));
			material.setTotalFee(rs.getBigDecimal(++columnIdx));
			material.setTotalGivemoney(rs.getBigDecimal(++columnIdx));

			Long creativeCount = creativeCountMap.get("prefix" + (null == material.getChncode() ? "" : material.getChncode()) + (null == material.getOsType() ? "" : material.getOsType().toString()));
			material.setTotalCreativeCount(creativeCount);
			// 素材使用相关指标
			material.setUsedRate(rate(material.getUsedCreativeCount(), material.getTotalCreativeCount()));
			// 计算广告日流水相关指标
			material.setShowClickRate(rate(material.getClickCount(), material.getShowCount()));
			// 计算设备归因相关指标
			material.setClickRegisterRate(rate(material.getRegisterCount(), material.getClickCount()));
			material.setClickActiveRate(rate(material.getActiveCount(), material.getClickCount()));
			material.setRegisterReducost(divide(material.getRudecost(), material.getRegisterCount()));
			// 计算付费相关指标
			material.setNewPayRate(rate(material.getNewPayUsers(), material.getRegisterCount()));
			material.setNewPayReducost(divide(material.getRudecost(), material.getNewPayUsers()));
			material.setFirstDayRoi(rate(material.getNewPayFee(), material.getRudecost()));
			material.setTotalRoi(rate(material.getTotalFee(), material.getRudecost()));
			// 计算留存相关指标
			material.setActiveStay2Rate(rate(material.getStay2UserCount(), material.getRegisterCount()));
			material.setActiveStay7Rate(rate(material.getStay7UserCount(), material.getRegisterCount()));
			material.setActiveStay30Rate(rate(material.getStay30UserCount(), material.getRegisterCount()));
			// 视屏播放相关指标
			material.setPlay10perRate(rate(material.getPlay10perCount(), material.getPlayTotalCount()));
			material.setPlay25perRate(rate(material.getPlay25perCount(), material.getPlayTotalCount()));
			material.setPlay50perRate(rate(material.getPlay50perCount(), material.getPlayTotalCount()));
			material.setPlay75perRate(rate(material.getPlay75perCount(), material.getPlayTotalCount()));
			material.setPlay95perRate(rate(material.getPlay95perCount(), material.getPlayTotalCount()));
			material.setPlay99perRate(rate(material.getPlay99perCount(), material.getPlayTotalCount()));
			material.setPlayOverRate(rate(material.getPlayOverCount(), material.getPlayTotalCount()));
			material.setPlayValidCost(divide(material.getRudecost(), material.getPlayValidCount()));
			material.setPlayValidRate(rate(material.getPlayValidCount(), material.getShowCount()));
			return material;
		};
	}

	private static Integer getInteger(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).intValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Integer");
	}

	private static Long getLong(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).longValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Long");
	}

	private static BigDecimal rate(Long divisor, Long dividend) {
		if (divisor == null || dividend == null || dividend == 0) {
			return null;
		}
		return divide1(BigDecimal.valueOf(divisor * 100), BigDecimal.valueOf(dividend));
	}

	private static BigDecimal rate(Long divisor, BigDecimal dividend) {
		if (divisor == null || dividend == null || dividend.doubleValue() == 0) {
			return null;
		}
		return divide1(BigDecimal.valueOf(divisor * 100), dividend);
	}

	private static BigDecimal rate(BigDecimal divisor, Long dividend) {
		if (divisor == null || dividend == null || dividend == 0) {
			return null;
		}
		return divide1(divisor.multiply(BigDecimal.valueOf(100)), BigDecimal.valueOf(dividend));
	}

	private static BigDecimal rate(BigDecimal divisor, BigDecimal dividend) {
		if (divisor == null || dividend == null || dividend.doubleValue() == 0) {
			return null;
		}
		return divide1(divisor.multiply(BigDecimal.valueOf(100)), dividend);
	}

	private static BigDecimal divide(Long divisor, Long dividend) {
		if (divisor == null || dividend == null || dividend == 0) {
			return null;
		}
		return divide1(BigDecimal.valueOf(divisor), BigDecimal.valueOf(dividend));
	}

	private static BigDecimal divide(Long divisor, BigDecimal dividend) {
		if (divisor == null || dividend == null || dividend.doubleValue() == 0) {
			return null;
		}
		return divide1(BigDecimal.valueOf(divisor), dividend);
	}

	private static BigDecimal divide(BigDecimal divisor, Long dividend) {
		if (divisor == null || dividend == null || dividend == 0) {
			return null;
		}
		return divide1(divisor, BigDecimal.valueOf(dividend));
	}

	private static BigDecimal divide(BigDecimal divisor, BigDecimal dividend) {
		if (divisor == null || dividend == null || dividend.doubleValue() == 0) {
			return null;
		}
		return divide1(divisor, dividend);
	}

	private static BigDecimal divide1(BigDecimal divisor, BigDecimal dividend) {
		return divisor.divide(dividend, 2, BigDecimal.ROUND_HALF_UP);
	}

	@Data
	public static class CreativeCount {
		private String groupKey;
		private String chncode;
		private Integer osType;
		private Long count;

		public String getKey() {
			return getKey(groupKey, chncode, osType);
		}

		public static String getKey(String groupKey, String chncode, Integer osType) {
			groupKey = null == groupKey ? "" : groupKey;
			chncode = null == chncode ? "" : chncode;
			String os = null == osType ? "" : osType.toString();
			return groupKey + chncode + os;
		}

	}

}
